/*
 * Copyright (c) 2024. Devtron Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package repository

import (
	"fmt"
	"github.com/devtron-labs/devtron/api/bean"
	"github.com/go-pg/pg"
)

const EmptyLikeRegex = "%%"

func BuildQueryForParentTypeCIOrWebhook(listingFilterOpts bean.ArtifactsListFilterOptions) (string, []interface{}) {
	commonPaginatedQueryPart, commonPaginatedQueryParams := " cia.image LIKE ?", []interface{}{listingFilterOpts.SearchString}
	orderByClause := " ORDER BY cia.id DESC"
	limitOffsetQueryPart, limitOffsetQueryParams := " LIMIT ? OFFSET ?", []interface{}{listingFilterOpts.Limit, listingFilterOpts.Offset}
	finalQuery := ""
	var finalQueryParams []interface{}
	var remainingQueryParams []interface{}
	if listingFilterOpts.ParentStageType == bean.CI_WORKFLOW_TYPE {
		selectQuery := " SELECT cia.* "
		remainingQuery := " FROM ci_artifact cia" +
			" INNER JOIN ci_pipeline cp ON (cp.id=cia.pipeline_id or (cp.id=cia.component_id and cia.data_source='post_ci' ) )" +
			" INNER JOIN pipeline p ON (p.ci_pipeline_id = cp.id and p.id=? )" +
			" WHERE "
		remainingQueryParams = []interface{}{listingFilterOpts.PipelineId}
		if len(listingFilterOpts.ExcludeArtifactIds) > 0 {
			remainingQuery += "cia.id NOT IN (?) AND "
			remainingQueryParams = append(remainingQueryParams, pg.In(listingFilterOpts.ExcludeArtifactIds))
		}

		countQuery := " SELECT count(cia.id)  as total_count"
		totalCountQuery := countQuery + remainingQuery + commonPaginatedQueryPart
		selectQuery = fmt.Sprintf("%s,(%s) ", selectQuery, totalCountQuery)
		finalQuery = selectQuery + remainingQuery + commonPaginatedQueryPart + orderByClause + limitOffsetQueryPart
	} else if listingFilterOpts.ParentStageType == bean.WEBHOOK_WORKFLOW_TYPE {
		selectQuery := " SELECT cia.* "
		remainingQuery := " FROM ci_artifact cia " +
			" WHERE cia.external_ci_pipeline_id = ? AND "
		remainingQueryParams = []interface{}{listingFilterOpts.ParentId}
		if len(listingFilterOpts.ExcludeArtifactIds) > 0 {
			remainingQuery += "cia.id NOT IN (?) AND "
			remainingQueryParams = append(remainingQueryParams, pg.In(listingFilterOpts.ExcludeArtifactIds))
		}

		countQuery := " SELECT count(cia.id)  as total_count"
		totalCountQuery := countQuery + remainingQuery + commonPaginatedQueryPart
		selectQuery = fmt.Sprintf("%s,(%s) ", selectQuery, totalCountQuery)
		finalQuery = selectQuery + remainingQuery + commonPaginatedQueryPart + orderByClause + limitOffsetQueryPart
	}
	finalQueryParams = append(finalQueryParams, remainingQueryParams...)
	finalQueryParams = append(finalQueryParams, commonPaginatedQueryParams...)
	finalQueryParams = append(finalQueryParams, remainingQueryParams...)
	finalQueryParams = append(finalQueryParams, commonPaginatedQueryParams...)
	finalQueryParams = append(finalQueryParams, limitOffsetQueryParams...)
	return finalQuery, finalQueryParams
}

func BuildQueryForArtifactsForCdStage(listingFilterOptions bean.ArtifactsListFilterOptions) (string, []interface{}) {
	// expected result -> will fetch all successfully deployed  artifacts ar parent stage plus its own stage. Along with this it will
	// also fetch all artifacts generated by plugin at pre_cd or post_cd process (will use data_source in ci artifact table for this)

	if listingFilterOptions.UseCdStageQueryV2 {
		return buildQueryForArtifactsForCdStageV2(listingFilterOptions)
	}

	var queryParams []interface{}

	commonQuery := " from ci_artifact LEFT JOIN cd_workflow ON ci_artifact.id = cd_workflow.ci_artifact_id" +
		" LEFT JOIN cd_workflow_runner ON cd_workflow_runner.cd_workflow_id=cd_workflow.id " +
		" Where (((cd_workflow_runner.id in (select MAX(cd_workflow_runner.id) OVER (PARTITION BY cd_workflow.ci_artifact_id) FROM cd_workflow_runner inner join cd_workflow on cd_workflow.id=cd_workflow_runner.cd_workflow_id))" +
		" AND ((cd_workflow.pipeline_id = ? and cd_workflow_runner.workflow_type = ?) OR (cd_workflow.pipeline_id = ? AND cd_workflow_runner.workflow_type = ? AND cd_workflow_runner.status IN ('Healthy','Succeeded') )))" +
		" OR (ci_artifact.component_id = ? and ci_artifact.data_source = ?))" +
		" AND (ci_artifact.image LIKE ?)"

	queryParams = append(queryParams,
		listingFilterOptions.PipelineId,
		listingFilterOptions.StageType,
		listingFilterOptions.ParentId,
		listingFilterOptions.ParentStageType,
		listingFilterOptions.ParentId,
		listingFilterOptions.PluginStage,
		listingFilterOptions.SearchString)

	if len(listingFilterOptions.ExcludeArtifactIds) > 0 {
		commonQuery += " AND ci_artifact.id NOT IN (?)"
		queryParams = append(queryParams, pg.In(listingFilterOptions.ExcludeArtifactIds))
	}

	totalCountQuery := "SELECT COUNT(DISTINCT ci_artifact.id) as total_count " + commonQuery
	selectQuery := "SELECT DISTINCT(ci_artifact.id), (" + totalCountQuery + ") "
	limitOffSetQuery := " order by ci_artifact.id desc LIMIT ? OFFSET ?"

	// Duplicate queryParams for the subquery
	finalQueryParams := append(queryParams, queryParams...)
	finalQueryParams = append(finalQueryParams, listingFilterOptions.Limit, listingFilterOptions.Offset)

	finalQuery := selectQuery + commonQuery + limitOffSetQuery
	return finalQuery, finalQueryParams
}

func buildQueryForArtifactsForCdStageV2(listingFilterOptions bean.ArtifactsListFilterOptions) (string, []interface{}) {
	var queryParams []interface{}

	whereCondition := " WHERE (id IN (" +
		" SELECT DISTINCT(cd_workflow.ci_artifact_id) as ci_artifact_id " +
		" FROM cd_workflow_runner" +
		" INNER JOIN cd_workflow ON cd_workflow.id = cd_workflow_runner.cd_workflow_id " +
		" AND (cd_workflow.pipeline_id = ? OR cd_workflow.pipeline_id = ?)" +
		"    WHERE (" +
		"            (cd_workflow.pipeline_id = ? AND cd_workflow_runner.workflow_type = ?)" +
		"            OR" +
		"            (cd_workflow.pipeline_id = ?" +
		"                AND cd_workflow_runner.workflow_type = ?" +
		"                AND cd_workflow_runner.status IN ('Healthy','Succeeded')" +
		"           )" +
		"      )   ) "

	queryParams = append(queryParams,
		listingFilterOptions.PipelineId,
		listingFilterOptions.ParentId,
		listingFilterOptions.PipelineId,
		listingFilterOptions.StageType,
		listingFilterOptions.ParentId,
		listingFilterOptions.ParentStageType)

	whereCondition += " OR (ci_artifact.component_id = ? AND ci_artifact.data_source = ?))"
	queryParams = append(queryParams, listingFilterOptions.ParentId, listingFilterOptions.PluginStage)

	if listingFilterOptions.SearchString != EmptyLikeRegex {
		whereCondition += " AND ci_artifact.image LIKE ?"
		queryParams = append(queryParams, listingFilterOptions.SearchString)
	}

	if len(listingFilterOptions.ExcludeArtifactIds) > 0 {
		whereCondition += " AND ci_artifact.id NOT IN (?)"
		queryParams = append(queryParams, pg.In(listingFilterOptions.ExcludeArtifactIds))
	}

	selectQuery := " SELECT ci_artifact.*, COUNT(id) OVER() AS total_count FROM ci_artifact"
	orderByAndPaginated := " ORDER BY id DESC LIMIT ? OFFSET ?"
	queryParams = append(queryParams, listingFilterOptions.Limit, listingFilterOptions.Offset)

	finalQuery := selectQuery + whereCondition + orderByAndPaginated
	return finalQuery, queryParams
}

func BuildQueryForArtifactsForRollback(listingFilterOptions bean.ArtifactsListFilterOptions) (string, []interface{}) {
	var queryParams []interface{}

	commonQuery := " FROM cd_workflow_runner cdwr " +
		" INNER JOIN cd_workflow cdw ON cdw.id=cdwr.cd_workflow_id " +
		" INNER JOIN ci_artifact cia ON cia.id=cdw.ci_artifact_id " +
		" WHERE cdw.pipeline_id = ? AND cdwr.workflow_type = ?"

	queryParams = append(queryParams, listingFilterOptions.PipelineId, listingFilterOptions.StageType)

	if listingFilterOptions.SearchString != EmptyLikeRegex {
		commonQuery += " AND cia.image LIKE ?"
		queryParams = append(queryParams, listingFilterOptions.SearchString)
	}

	if len(listingFilterOptions.ExcludeWfrIds) > 0 {
		commonQuery += " AND cdwr.id NOT IN (?)"
		queryParams = append(queryParams, pg.In(listingFilterOptions.ExcludeWfrIds))
	}

	totalCountQuery := " SELECT COUNT(cia.id) as total_count " + commonQuery
	orderByQuery := " ORDER BY cdwr.id DESC "
	limitOffsetQuery := " LIMIT ? OFFSET ?"

	// Duplicate queryParams for the subquery
	finalQueryParams := append(queryParams, queryParams...)
	finalQueryParams = append(finalQueryParams, listingFilterOptions.Limit, listingFilterOptions.Offset)

	finalQuery := " SELECT cdwr.id as cd_workflow_runner_id,cdwr.triggered_by,cdwr.started_on,cia.*,(" + totalCountQuery + ") " + commonQuery + orderByQuery + limitOffsetQuery
	return finalQuery, finalQueryParams
}
